﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using DAL.Entities;

namespace DAL.Entities_DAL
{
    public class Nhanvien_DAL: BaseDAL, UTL.IBaseDAL
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            var sql = @"SELECT  *  FROM NHAN_VIEN";
            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            string sql = @"DELETE FROM NHAN_VIEN WHERE MA_NV = '{0}'";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (Nhanvien)obj;
            string sql = @"INSERT INTO NHAN_VIEN(MA_NV, HOTEN_NV, TEN_DANG_NHAP, NGAY_SINH, DIA_CHI,  SO_DIEN_THOAI, HKTT, CHUC_VU, LUONG) VALUES ('{0}', N'{1}', '{2}', N'{3}', N'{4}', '{5}', N'{6}', N'{7}', '{8}' )";
            sql = string.Format(sql, o.MA_NV, o.HOTEN_NV, o.TEN_DANG_NHAP, o.NGAY_SINH, o.DIA_CHI, o.SO_DIEN_THOAI, o.HKTT, o.CHUC_VU, o.LUONG);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (Nhanvien)obj;
            string sql = @"set dateformat dmy; UPDATE NHAN_VIEN SET HOTEN_NV=N'{1}', TEN_DANG_NHAP='{2}', NGAY_SINH='{3}', DIA_CHI = N'{4}',  SO_DIEN_THOAI='{5}', HKTT= N'{6}', CHUC_VU= N'{7}', LUONG= '{8}'  WHERE MA_NV = '{0}' ";
            sql = string.Format(sql, o.MA_NV, o.HOTEN_NV, o.TEN_DANG_NHAP, o.NGAY_SINH, o.DIA_CHI, o.SO_DIEN_THOAI, o.HKTT, o.CHUC_VU, o.LUONG);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        //tim ma lon nhat

        public DataTable Select_max()
        {
            var sql = @"SELECT MAX(MA_NV) as id FROM NHAN_VIEN";
            return ExecuteQuery(sql);
        }

        //load ten dang nhap len combobox

        public DataTable Select_TenDN()
        {
            string sql = @" 
                            SELECT TEN_DANG_NHAP as ten FROM TAI_KHOAN 
                            ";
            return ExecuteQuery(sql);
        }

        //dem so nhan vien

        public string Select_count()
        {
            string sql = @"SELECT COUNT(MA_NV) as id FROM NHAN_VIEN";
            var tb = ExecuteQuery(sql);
            return tb.Rows.Count > 0 ? tb.Rows[0]["id"] + "" : "0";
        }
    }
}
